
package cn.ainit.base.approve;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpSession;
import org.hibernate.Query;
import org.springframework.stereotype.Repository;
import cn.ainit.base.approve.entity.ApproveREG;
import cn.ainit.base.approve.entity.SysApprove;
import cn.ainit.common.ReturnObject;
import cn.ainit.sys.common.dao.BusinessBaseDao;
import cn.ainit.sys.menu.SessionCont;

@Repository("approveDao")
public class ApproveDaoImpl extends BusinessBaseDao implements ApproveDao {

	@Override
	public List<SysApprove> listApproves(String djId) throws Exception {
		List<SysApprove> list = new ArrayList<SysApprove>();
		try {
			String hql = "from SysApprove s where s.djId =? order by s.approveLevel";
			Query query = sessionFactory.getCurrentSession().createQuery(hql);
			query.setString(0, djId);
			list = query.list();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public boolean updApprove(String sql) throws Exception {
		return this.executeSQL(sql);
	}
	
	

	@Override
	public SysApprove getOneApprove(String djId, Integer djLevel)
	throws Exception {
		List<SysApprove> list = new ArrayList<SysApprove>();
		try {
			String hql = "from SysApprove s where s.djId =? and s.approveLevel =?";
			Query query = sessionFactory.getCurrentSession().createQuery(hql);
			query.setString(0, djId);
			query.setInteger(1, djLevel);
			list = query.list();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list.get(0);
	}



	@Override
	public String getApproveTable(String type) throws Exception {
		List<ApproveREG> list = new ArrayList<ApproveREG>();
		try {
			String hql = "from ApproveREG s where s.djType =?";
			Query query = sessionFactory.getCurrentSession().createQuery(hql);
			query.setString(0, type);
			list = query.list();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list.get(0).getTableWhere();
	}
	@Override
	public String getValue(String sql) throws Exception {
		return this.execSqlForString(sql);
	}

	/**
	 * 审批示根据ds_id 得到ds_name
	 * @param sql
	 * @return
	 * author:cff
	 * date:2014/3/29 11:09
	 * @throws Exception
	 */
	public String getDsName(String dsId) throws Exception{
		try{
			String sql="select s.ds_name from sys_datastore s where s.ds_id='"+dsId+"'";
			String dsName=this.jdbcTemplate.queryForObject(sql,String.class);
			return dsName;
		}catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}



	/***
	 * 根据单据的id,djLevel,djType得到单据的列表
	 */
	@Override
	public SysApprove getApproveByPks(String djId, String djLevel, String djType) throws Exception {
		List<SysApprove> list = new ArrayList<SysApprove>();
		try {
			String hql = "from SysApprove s where s.djId =? and s.approveLevel =? and s.djType=?";
			Query query = sessionFactory.getCurrentSession().createQuery(hql);
			query.setString(0, djId);
			query.setString(1, djLevel);
			query.setString(2, djType);
			list = query.list();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list.get(0);
	}



	/***
	 * 得到指定的单据的最小的等级
	 */
	@Override
	public Integer maxDjLevel(SysApprove sysApprove) throws Exception {
		Integer maxDjLevel=null;
		try{
			String sql="select max(APPROVE_LEVEL) from sys_approve s where DJ_ID='"+sysApprove.getDjId()+"' and ds_id='"+sysApprove.getDsId()+"'";
			String djMaxLevel=this.jdbcTemplate.queryForObject(sql,String.class);
			maxDjLevel=maxDjLevel.parseInt(djMaxLevel);
		}catch (Exception e) {
			e.printStackTrace();
			return null;
		}
		return maxDjLevel;
	}



	/***
	 * 得到指定的单据的最大的等级
	 * 
	 */
	@Override
	public Integer minDjLevel(String djId)throws Exception {
		Integer minDjLevel=null;
		try {
			String sql="select min(APPROVE_LEVEL) from sys_approve s where DJ_ID='"+djId+"'";
			String djMinLevel=this.jdbcTemplate.queryForObject(sql,String.class);
			minDjLevel=minDjLevel.parseInt(djMinLevel);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return minDjLevel;
	}



	/***
	 * 驳回全部
	 */
	@Override
	public Integer upAllApplyByDjId(String djId) throws Exception{
		Integer count=null;
		try {
			String sql="update sys_approve set IS_EXAMINE='2' where DJ_ID='"+djId+"'";
			count=this.jdbcTemplate.update(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return count;

	}

	/***
	 * 驳回上一级
	 */
	@Override
	public Integer upApplyByIdLevel(String djId, String djLevel)
	throws Exception {
		Integer count=null;
		try {
			String sql="update sys_approve set IS_EXAMINE='2' where DJ_ID='"+djId+"' and approve_level<='"+djLevel+"'";
			count=this.jdbcTemplate.update(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return count;
	}

	/***
	 * 将单据的相关参数传入 确保 审批表有数据
	 */
	@Override
	public String getApprove(String djId,String djType,String djCode)
	throws Exception {
		String flagStr = "";
		try {
			String sql = "select PK_COM_APPROVE.uf_GetDjApprove('"+djId+"','"+djType+"','"+djCode+"','"+djCode+"') flag from dual";
			flagStr = (String) this.getJdbcTemplate().queryForList(sql).get(0).get("flag");
		} catch (Exception e) {
			e.getMessage();
			throw e;
		}
		return flagStr;
	}


	/***
	 * 很据djType得到dj的主键
	 * @param djType
	 * @return
	 * @throws Exception
	 */
	@Override
	public String getPKByDjType(String djType) throws Exception {
		String pkCol=null;
		try {
			String sql="select distinct(pk_Column) from SYS_APPROVE_REG where dj_Type='"+djType+"'";
			pkCol=this.jdbcTemplate.queryForObject(sql,String.class);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return pkCol;
	}

	@Override
	public List<SysApprove> dataById(String id,String dsId) throws Exception {
		List<SysApprove> list=null;
		try {
			String hql = "from SysApprove s where s.djId =? and s.dsId=? order by s.approveLevel  ";
			Query query = sessionFactory.getCurrentSession().createQuery(hql);
			query.setString(0, id);
			query.setString(1, dsId);
			list = query.list();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public String  getMaxApplyLevelByDsId(String dsId,String param1) throws Exception{
		try{
			String sql="select max(APPROVE_LEVEL) from SYS_APPROVE_REG s where DS_ID='"+dsId+"'";
			String djMaxLevel=this.jdbcTemplate.queryForObject(sql,String.class);
			return djMaxLevel;
		}catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}

	@Override
	public ReturnObject produceApproveList(String djId,String dsId,String djType,String djCode,HttpSession session) throws Exception{
		boolean flag=false;
		Connection conn= null;
		ReturnObject returnObject=new ReturnObject();
		CallableStatement cs =null;
		try {
			conn= this.getJdbcTemplate().getDataSource().getConnection();
			String callName = "{Call PK_COM_APPROVE.up_applyApprove(?,?,?,?,?,?)}";
			cs = conn.prepareCall(callName);
			cs.setString(1,djId);
			cs.setString(2,dsId);
			cs.setString(3,djType);
			cs.setString(4,djCode);
			cs.setString(5,(String) session.getAttribute(SessionCont.uid));
			cs.registerOutParameter(6,Types.VARCHAR);
			cs.execute();
			returnObject.setMsg(cs.getString(6));
		} catch(Exception e) {
			e.printStackTrace();
		}finally{
			if(cs!=null){
				cs.close();
			}if(conn!=null){
				conn.close();
			}
		}
		return returnObject;
	}


	@Override
	public boolean sureApproveShowMsg(String djId,Integer approveLevel,String dsId,HttpSession session) throws Exception {
		Connection conn= null;
		CallableStatement cs =null;
		boolean flag=false;
		try {
			conn= this.getJdbcTemplate().getDataSource().getConnection();
			String callName = "{Call PK_COM_APPROVE.up_SaveApproveToMsg(?,?,?,?,?)}";
			cs = conn.prepareCall(callName);
			cs.setString(1,djId);
			cs.setInt(2,approveLevel);
			cs.setString(3,dsId);
			cs.setString(4, (String)session.getAttribute(SessionCont.uid));
			cs.registerOutParameter(5,Types.VARCHAR);
			cs.execute();
			System.out.println(""+cs.getString(5));;
			flag=true;
		} catch (Exception e) {
			e.getMessage();
			flag=false;
			throw e;
		}finally{
			if(cs!=null){
				cs.close();
			}if(conn!=null){
				conn.close();
			}
		}
		return flag;
	}



	@Override
	public String getExamineManFkVal(Integer level,String dsId,String param) throws Exception{
		String examineFkVal=null;
		try {
			String sql="select examineMan from ApproveREG s where s.dsId=? and s.approveLevel=?";
			Query query=sessionFactory.getCurrentSession().createQuery(sql);
			query.setString(0, dsId);
			query.setInteger(1, level);
			examineFkVal=(String)query.list().get(0);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return examineFkVal;
	}

	@Override
	public List getItemsCodes (String sql) throws Exception{
		List list= null;
		try {
			if(sql!=null&&sql.trim()!=""){
				list=this.jdbcTemplate.queryForList(sql);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public Boolean sureSaveAppr(SysApprove reApp,HttpSession session)
	throws Exception {
		boolean flag=false;
		try {
			//判断本审核单据的审核状态是否已经被审核
			String checkSql = " SELECT 1 FROM DUAL ";
			//如果没有被审核，更新本数据
			String upSql = "update sys_approve set IS_EXAMINE = ?," +
			"EXAMINE_DATE = ?," +
			"EXAMINE_CONTENT=?," +
			"EXAMINE_MAN=? " +
			"where  dj_id = ? " +
			"and approve_level = ?" +
			"and ds_id=?"; 
			int a=this.jdbcTemplate.update(upSql, new Object[]{reApp.getIsExamine(),new Timestamp(System.currentTimeMillis()),reApp.getExamineContent(),(String)session.getAttribute(SessionCont.uid),reApp.getDjId(),reApp.getApproveLevel(),reApp.getDsId()});
			flag=a>0?true:false;
		} catch (Exception e) {
			e.printStackTrace();
			flag=false;
		}
		return flag;
	}



	@Override
	public String checkApprovePass(Integer level, String djId,String dsId,String param) {
		String sql="select s.is_examine from sys_approve s where s.dj_id='"+djId+"' and s.approve_level='"+level+"' and s.ds_id='"+dsId+"'";
		String djMaxLevel=this.jdbcTemplate.queryForObject(sql,String.class);
		return djMaxLevel;
	}


	@Override
	public boolean updateDjVerify(String table, String pkName,String pkVal,HttpSession session) throws Exception {
		boolean flag=false;
		Connection conn = null;
		PreparedStatement stmt = null;
		try {
			conn =  this.getJdbcTemplate().getDataSource().getConnection();
			String sql="update "+table+" s set s.is_verify=1,s.verify_man=?,s.verify_date=? where  s."+pkName+"=?";
			stmt = conn.prepareCall(sql);
			stmt.setString(1, (String)session.getAttribute(SessionCont.uid));
			stmt.setTimestamp(2, new Timestamp(System.currentTimeMillis()));
			stmt.setString(3,pkVal);
			flag=stmt.executeUpdate()==1?true:false;
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			if (stmt != null) {
				stmt.close();
			}
			if(conn != null){
				conn.close();
			}
		}
		return flag;
	}


	@Override
	public SysApprove getMinLevelAppObj(String djId,String dsId) throws Exception {
		Integer minLevel=minDjLevel(djId);
		String sql="from SysApprove s where s.djId='"+djId+"' and s.approveLevel="+minLevel+" and s.dsId='"+dsId+"'";
		Query query=sessionFactory.getCurrentSession().createQuery(sql);
		SysApprove sysApprove=(SysApprove) query.list().get(0);
		return sysApprove;
	}



	@Override
	public ReturnObject  updateIsVerify(Integer approveLevel,String djId,String dsId,HttpSession session) throws Exception {
		ReturnObject returnObject=new ReturnObject();
		Connection conn= null;
		CallableStatement cs =null;
		String isVerify=null;
		/***
		 * 审批结果的值类型
		 */
		String verifyPass="S001";  //审批通过
		String verifyNoPass="S002";  //审批不通过
		String verifyRefuse="S003";  //驳回上一级(只有二审和三审有驳回上一级)
		String verifyReject ="S004";  //审批被驳回(只有二审和三审有驳回上一级)
		try {
			//审批通过,把当前的等级的下一等级的is_verify更新成带审批状态
			conn= this.getJdbcTemplate().getDataSource().getConnection();
			String callName = "{Call PK_COM_APPROVE.up_modifyDjVerify(?,?,?,?,?,?)}";
			cs = conn.prepareCall(callName);
			cs.setInt(1,approveLevel);
			cs.setString(2,djId);
			cs.setString(3,dsId);
			cs.setString(4,(String) session.getAttribute(SessionCont.uid));
			cs.registerOutParameter(5,Types.VARCHAR);//VO_CHECKOUT  OUT VARCHAR2,
			cs.registerOutParameter(6,Types.VARCHAR);// VO_VERIFY    OUT VARCHAR2
			cs.execute();
			returnObject.setMsg(cs.getString(5));
			returnObject.setIsVerify(cs.getString(6));
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			if(cs!=null){
				cs.close();
			}if(conn!=null){
				conn.close();
			}
		}
		return returnObject;
	}



	@Override
	public String delApproveFlow(String regId, Integer curLevel, String flowId)
	throws Exception {
		Connection conn=null;
		CallableStatement cs=null;
		String msg=null;
		try {
			conn=this.jdbcTemplate.getDataSource().getConnection();
			String callName="{call PK_COM_APPROVE.upDelApproveFlow(?,?,?,?)}";
			cs=conn.prepareCall(callName);
			cs.setString(1,regId);
			cs.setInt(2,curLevel);
			cs.setString(3,flowId);
			cs.registerOutParameter(4, Types.VARCHAR);
			cs.execute();
			msg=cs.getString(4);
		} catch (Exception e) {
			e.printStackTrace();
			msg="亲，删除流程环节出现异常，请联系开发";
		}finally{
			if(cs!=null){
				cs.close();
			}
			if(conn!=null){
				conn.close();
			}
		}
		return msg;
	}


	@Override
	public Integer getIsApproveRight(String soureId, String djId, String dsId,HttpSession session)
	throws Exception {
		Connection conn=null;
		CallableStatement cs=null;
		Integer isRight=null;
		try {
			conn=this.jdbcTemplate.getDataSource().getConnection();
			String callName="{call PK_COM_APPROVE.up_isApproveRight(?,?,?,?,?)}";
			cs=conn.prepareCall(callName);
			cs.setString(1,soureId);
			cs.setString(2,djId);
			cs.setString(3, dsId);
			cs.setString(4,(String)session.getAttribute(SessionCont.uid));
			System.out.println("= =======================点击确认审批的测试数据：soureId："+soureId+",djId:"+djId+",dsId:"+dsId+",uid:"+(String)session.getAttribute(SessionCont.uid));
			cs.registerOutParameter(5, Types.INTEGER);
			cs.execute();
			isRight=cs.getInt(5);
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			if(cs!=null){
				cs.close();
			}
			if(conn!=null){
				conn.close();
			}
		}
		return isRight;
	}



	@Override
	public ReturnObject sureRejectByLead(String djId,String dsId,String code,HttpSession session) throws Exception{
		Connection conn=null;
		CallableStatement cs=null;
		Integer isRight=null;
		ReturnObject reObject=new ReturnObject();
		try {
			//调用审批通过之后，最后一级的领导驳回的存储过程
			String sql="SELECT s.procedure FROM SYS_REJECT_PROCEDURES s WHERE s.ds_id=? AND s.code=?";
			List<Map<String, Object>> list=this.jdbcTemplate.queryForList(sql,new Object[]{dsId,code});
			if(list!=null&&list.size()>0){
				String procName=list.get(0).get("PROCEDURE").toString();
				conn=this.jdbcTemplate.getDataSource().getConnection();
				String callName="{call "+procName+"(?,?,?,?,?,?)}";
				cs=conn.prepareCall(callName);
				cs.setString(1,djId);
				cs.setString(2, dsId);
				cs.setString(3, (String)session.getAttribute(SessionCont.uid));
				cs.setString(4, "");
				cs.registerOutParameter(5, Types.VARCHAR);
				cs.registerOutParameter(6, Types.VARCHAR);
				cs.execute();
				reObject.setIsResult(cs.getString(5));
				reObject.setMsg(cs.getString(6));
			}
			else{
				return null;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			if(cs!=null){
				cs.close();
			}
			if(conn!=null){
				conn.close();
			}
		}
		return reObject;
	}

	/***
	 * 根据dsid判断单据是否有审批
	 * @param dsId
	 * @param param
	 * @return
	 * @throws Exception
	 * @author feifei
	 * @date: 2015-4-2 上午11:15:08 
	 */
	@Override
	public String isRejectByDsId(String dsId, String param) throws Exception {
		String sql="SELECT s.is_reject  FROM sys_datastore s WHERE s.ds_id=?";
		return this.jdbcTemplate.queryForObject(sql, String.class, new Object[]{dsId});
	}

	/***
	 * 验证数据的完成性
	 * @param djId
	 * @param dsId
	 * @param prams
	 * @return
	 * @throws Exception
	 * @author feifei
	 * @date: 2015-4-9 下午06:11:45 
	 */
	@Override
	public String checkSubmitData(String djId, String dsId, String prams,HttpSession session)
			throws Exception {
		Connection conn=null;
		CallableStatement cs=null;
		String subMsg=null;
		try {
			String callName="{call PK_COM_APPROVE.UP_CHECKVALIDITY(?,?,?,?)}";
			conn=this.jdbcTemplate.getDataSource().getConnection();
			cs=conn.prepareCall(callName);
			cs.setString(1, djId);
			cs.setString(2, dsId);
			cs.setString(3,(String)session.getAttribute(SessionCont.uid));
			cs.registerOutParameter(4, Types.VARCHAR);
			cs.execute();
			subMsg=cs.getString(4);
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			if(cs!=null){
				cs.close();
			}if(conn!=null){
				conn.close();
			}
		}
		return subMsg;
	}

	@Override
	public String getMaster(String djId, String dsId, HttpSession session)
			throws Exception {
		Connection conn=null;
		CallableStatement cs=null;
		String isMaster=null;
		try {
			String callName="{call PK_MODIFY_PERMS.UP_GETMASTER(?,?,?,?)}";
			conn=this.jdbcTemplate.getDataSource().getConnection();
			cs=conn.prepareCall(callName);
			cs.setString(1, djId);
			cs.setString(2, dsId);
			cs.setString(3,(String)session.getAttribute(SessionCont.uid));
			cs.registerOutParameter(4, Types.VARCHAR);
			cs.execute();
			isMaster=cs.getString(4);
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			if(cs!=null){
				cs.close();
			}if(conn!=null){
				conn.close();
			}
		}
		return isMaster;
	}

}







